package com.ycxh.module.dbtoword.util;

import cn.hutool.core.util.StrUtil;
import com.ycxh.module.dbtoword.domain.TableFileds;
import com.ycxh.module.dbtoword.domain.Tables;
import org.apache.commons.lang.StringUtils;

import java.io.FileWriter;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 生成sql语句工具类
 * 使用mybatis-plus之后，基础的增删改查都有了
 * 但接手以前的老项目，这些sql还是要自己写
 * 而且没开mybatis转驼峰，有些实体类下划线，有些驼峰，风格不统一，维护起来特别费劲，比如加个表
 * 另外，内外网，内网用的国产数据库达梦，外网用的mysql，维护2套代码也超麻烦
 *
 * Mybatis代码自动生成工具，生成批量插入和批量更新方法 https://blog.csdn.net/xiaostuart/article/details/108079684
 * https://github.com/derotyoung/mybatis-generator-gui-plus
 * https://github.com/zouzg/mybatis-generator-gui
 * idea插件：MyBatisCodeHelperPro 收费
 */
public class GenerateSqlUtil {

    private static String url = "jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&nullCatalogMeansCurrent=true&databaseTerm=SCHEMA";
    // 数据库用户名
    private static String userName = "root";
    // 数据库密码
    private static String password = "tiger";
    // 数据库名称
    private static String dbName = "fgap";

    // 生成创建触发器语句
    // canal 基于业务 trigger（触发器） 获取增量变更
    // mysql:通过JDBC接口执行创建触发器的SQL语句
    // https://blog.csdn.net/10km/article/details/123704994
    public static void main(String[] args) throws Exception {

        String sql = "";

        // 生成基础的查询语句
        // String sql = createBaseSelectSql(false);
        // sql = createBaseSelectSql(true);

        // 批量给所有表增加一个同样的字段
        // sql = createAddColumnSqlTable();

        // mysql判断表里是否有这个字段，有则生成达梦添加字段语句
        // sql = createAddColumnSqlCompare();

        // 生成触发器
        // sql = createTriggerSql();
        // sql = createTriggerSql2();

        // 生成mybatis批量插入语句 动态sql
        // sql = createBatchInsertSql(false);
        // sql = createBatchInsertSql(true);
        sql = createUpdateSqlCompare();
        // sql = createUpdateDateTimeSqlCompare();

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        if (StringUtils.isNotBlank((sql))) {
            FileWriter fileWriter1 = new FileWriter("D:\\createSql-" + df.format(new Date()) + ".txt");
            fileWriter1.write(sql);
            fileWriter1.flush();
            fileWriter1.close();
        } else {
            System.out.println("sql内容为空");
        }
    }

    /**
     * 生成基础的查询语句 <sql id="Base_Column_List">
     *
     * @param mapUnderscoreToCamelCase 是否下划线转驼峰
     * @return sql语句
     */
    private static String createBaseSelectSql(boolean mapUnderscoreToCamelCase) throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            Tables table = tables.get(i);
            // 表名
            String tableName = table.getName();
            sql += "-- " + tableName + " \n";

            // 获取指定表字段信息
            List<TableFileds> filedList = jdbcMysql.getTable(dbName, tableName);
            for (int j = 0; j < filedList.size(); j++) {
                TableFileds filed = filedList.get(j);
                sql += " " + filed.getField();
                if (mapUnderscoreToCamelCase) {
                    // 驼峰与下划线互转 Hutool工具类
                    // https://developer.aliyun.com/article/927183
                    // https://blog.csdn.net/MyBlogHiHi/article/details/124611925
                    // StrUtil.toUnderlineCase(filed.getField());
                    sql += " " + StrUtil.toCamelCase(filed.getField());
                }
                if (j < filedList.size() - 1) {
                    sql += ",";
                }
            }
            sql += "\n\n";
        }
        return sql;
    }

    /**
     * 生成添加字段
     * 批量给所有表增加一个同样的字段
     * 比如内外网数据同步标志
     */
    private static String createAddColumnSqlTable() {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            // ALTER TABLE 表名
            // ADD COLUMN 字段名 int(1) NULL DEFAULT 1 COMMENT '注释';
            // 表名
            String tableName = tables.get(i).getName();
            // 字段名
            String fieldName = "is_send";
            // String type = "int(1)";
            // 达梦 int不带长度
            String type = "int";
            String defaultValue = "1";
            String comment = "'提取状态，0-同步导入，1-待提取，2-已提取，3-不用提取，4-待删除，5-待修改'";
            // mysql
            if (tableName.startsWith("")) {
                // if (tableName.startsWith("screen_")) {
                sql += "ALTER TABLE " + tableName + " ADD COLUMN " + fieldName + " " + type + (StringUtils.isNotEmpty(defaultValue) ? " DEFAULT " + defaultValue : "");
                if (StringUtils.isNotBlank(comment)) {
                    sql += " COMMENT " + comment;
                }
                sql += ";\n\n";
            }
            // 达梦
            // if (tableName.startsWith("")) {
            // // if (tableName.startsWith("screen_")) {
            //     sql += "ALTER TABLE " + tableName + " ADD COLUMN " + fieldName + " " + type + (StringUtils.isNotEmpty(defaultValue) ? " DEFAULT " + defaultValue : "") + ";\n";
            //     if (StringUtils.isNotBlank(comment)) {
            //         sql += "COMMENT ON COLUMN " + tableName + "." + fieldName + " IS " + comment + ";\n";
            //     }
            //     sql += "\n";
            // }
        }
        return sql;
    }

    /**
     * 生成添加字段
     * mysql判断表里是否有这个字段，有则生成达梦添加字段语句
     * 类似navicat同步表结构
     */
    private static String createAddColumnSqlCompare() throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();

            List<TableFileds> fileds = jdbcMysql.getTable(dbName, tableName);
            for (TableFileds filed : fileds) {
                // 字段名
                String fieldName = filed.getField();
                String addFieldName = "id";
                // String addFieldName = "is_send";
                if (addFieldName.equals(fieldName)) {
                    // 注释
                    String comment = filed.getComment();
                    // 默认值
                    String defaultValue = filed.getDefault();
                    String type = filed.getType();
                    // 达梦 int 不带精度
                    if (type.contains("int(")) {
                        // int tinyint(4) bigint
                        type = type.substring(0, type.indexOf("("));
                    }

                    sql += "ALTER TABLE " + tableName + " ADD " + addFieldName + " " + type + (StringUtils.isNotEmpty(defaultValue) ? " DEFAULT " + defaultValue : "") + ";\n";
                    if (StringUtils.isNotBlank(comment)) {
                        sql += "COMMENT ON COLUMN " + tableName + "." + fieldName + " IS '" + comment + "';\n";
                    }
                    sql += "\n";
                    break;
                }
            }
        }
        return sql;
    }

    /**
     * 生成更新语句
     * mysql判断表里是否有这个字段，有则生成更新语句--更新值
     * 字段存在默认值，但是不想要初始化的时候为默认值
     */
    private static String createUpdateSqlCompare() throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        Connection conn = jdbcMysql.getConn();

        List<Tables> tables = jdbcMysql.getAllTables(dbName, conn);
        // 要判断那些字段
        List<String> fieldNameList = Arrays.asList("is_send");

        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();
            // if (tableName.startsWith("sc_")) {
            //     // 排除某个前缀的表
            //     continue;
            // }

            List<TableFileds> fileds = jdbcMysql.getTable(dbName, tableName, conn);
            // 当前表存在那些目标字段
            List<String> exists = new ArrayList();
            for (TableFileds filed : fileds) {
                // 字段名
                String fieldName = filed.getField();
                if (fieldNameList.contains(fieldName)) {
                    exists.add(fieldName);
                }
            }
            if (exists.size() > 0) {
                sql += "UPDATE " + tableName + " set ";

                for (int j = 0; j < exists.size(); j++) {
                    if (j > 0) {
                        sql += ",";
                    }
                    sql += exists.get(j) + " = 2";
                }
                sql += ";\n";
                sql += "\n";
            }
        }
        // 关闭连接
        jdbcMysql.closeAll(null, null, conn);
        return sql;
    }

    /**
     * 生成更新语句
     * mysql判断表里是否有这个字段，有则生成更新语句--更新值
     * jdbc时区设置错误，导致数据库时间晚8H，手动修正
     *
     * 关于解决MySQL插入时间差八小时问题
     * https://segmentfault.com/a/1190000021285927
     * jdbc url serverTimezone=UTC
     * 数据库时区为 CST 中国标准时间：China Standard Time UT+8:00
     * java new Date() 保存到数据库少8h
     * 使用now() 让数据库自动生成不会，与实际一致
     */
    private static String createUpdateDateTimeSqlCompare() throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);

        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();

            List<TableFileds> fileds = jdbcMysql.getTable(dbName, tableName);
            // 当前表存在那些目标字段
            List<String> exists = new ArrayList();
            for (TableFileds filed : fileds) {
                if (filed.getType().startsWith("datetime")) {
                    // 假如是日期字段
                    // 字段名
                    String fieldName = filed.getField();
                    exists.add(fieldName);
                }
            }
            if (exists.size() > 0) {
                sql += "UPDATE " + tableName + " set ";

                for (int j = 0; j < exists.size(); j++) {
                    if (j > 0) {
                        sql += ",";
                    }
                    String filedName = exists.get(j);
                    sql += filedName + " = DATE_ADD(" + filedName + ", INTERVAL 8 HOUR)";
                }
                sql += ";\n";
                sql += "\n";
            }
        }
        return sql;
    }

    /**
     * 生成触发器
     */
    private static String createTriggerSql() {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();
            System.out.println(tableName);
            if (tableName.startsWith("")) {
                // if (tableName.startsWith("screen_")) {
                // 最简单是使用账号区分是不是同步操作
                // update触发器 前置触发器 更新标志

                // CREATE TRIGGER `sync_tr_screen_test_u`
                // BEFORE UPDATE ON `screen_test` FOR EACH ROW BEGIN
                // IF NEW.is_send is null or (NEW.is_send < 4 and OLD.is_send < 4) -- 4-待删除，5-待修改 用于同步操作绕过触发器
                // THEN
                // set NEW.is_send = 1;
                // END IF;
                // END;

                // 触发器名称
                String triggerName = "sync_tr_" + tableName + "_u";
                // 存在则删除
                sql += "DROP TRIGGER IF EXISTS " + triggerName + "; \n";
                sql += "CREATE trigger " + triggerName + " \n" +
                        " BEFORE UPDATE ON " + tableName + " \n" +
                        " FOR EACH ROW  \n" +
                        " BEGIN \n" +
                        " IF NEW.is_send is null or (NEW.is_send < 4 and OLD.is_send < 4) \n" +
                        " THEN \n" +
                        " set NEW.is_send = 1;\n" +
                        " END IF;\n" +
                        " END;\n\n";

                // delete触发器

                // CREATE TRIGGER `sync_tr_screen_test_d`
                // AFTER DELETE ON `screen_test` FOR EACH ROW BEGIN
                // IF OLD.is_send is null or (NEW.is_send < 4 and OLD.is_send < 4)
                // THEN
                // INSERT INTO sync_data(`id`, `sync_table_name`, `sync_id`, `TYPE`, `sync_old_id`, `is_sync`, `create_time`) VALUES (UUID(), "screen_test", OLD.id, "delete", OLD.id, 1, NOW());
                // END IF;
                // END;

                triggerName = "sync_tr_" + tableName + "_d";
                sql += "DROP TRIGGER IF EXISTS " + triggerName + "; \n";
                sql += "CREATE trigger " + triggerName + " \n" +
                        " AFTER DELETE ON " + tableName + " \n" +
                        " FOR EACH ROW  \n" +
                        " BEGIN \n" +
                        " IF OLD.is_send is null or OLD.is_send < 4 \n" +
                        " THEN \n" +
                        " INSERT INTO sync_data(`id`, `sync_table_name`, `sync_id`, `TYPE`, `sync_old_id`, `is_sync`, `create_time`) \n" +
                        // 达梦字段名不能用`，要用双引号 也可以先生成，再用文本编辑器的替换功能修改下
                        // " INSERT INTO sync_data(id, sync_table_name, sync_id, TYPE, sync_old_id, is_sync, create_time) \n" +
                        " VALUES (UUID(), '" + tableName + "', OLD.id, 'delete', OLD.id, 1, NOW()); \n" +
                        // 达梦没有UUID函数，可以用NEWID
                        // " VALUES (NEWID(), '" + tableName + "', OLD.id, 'delete', OLD.id, 1, NOW()); \n" +
                        " END IF;\n" +
                        " END;\n\n";
            }
        }
        return sql;
    }

    // 生成触发器
    private static String createTriggerSql2() throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();
            System.out.println(tableName);
            if (tableName.startsWith("sys_")) {
                // insert触发器
                sql += "DROP TRIGGER IF EXISTS " + "sync_tr_" + tableName + "_i; \n";
                sql += "CREATE trigger " + "sync_tr_" + tableName + "_i \n" +
                        " AFTER INSERT ON " + tableName + " \n" +
                        " FOR EACH ROW  \n" +
                        " BEGIN \n" +
                        " IF NEW.is_send is null or NEW.is_send<>0 \n" +
                        " THEN \n" +
                        "  INSERT INTO `SYN_DATA` (`SYNC_DATE`,\n" +
                        "  `SYNC_TABLE_NAME`,`SYNC_TYPE`,`SYNC_PRIKEY_VALUE`,\n" +
                        "  `SYNC_PRIKEY_OLDVALUE`,`SYNC_ACTION_TAG`,`SYNC_STATUS_TAG`) \n" +
                        "  values (now(),'" + tableName + "','INCREMENT',NEW.`id`,NEW.`id`,'INSERT',0); \n" +
                        "  END IF;\n" +
                        "  END;\n\n";
                // update触发器
                sql += "DROP TRIGGER IF EXISTS " + "sync_tr_" + tableName + "_u; \n";
                sql += "CREATE trigger " + "sync_tr_" + tableName + "_u \n" +
                        " AFTER UPDATE ON " + tableName + " \n" +
                        " FOR EACH ROW  \n" +
                        " BEGIN \n" +
                        " IF NEW.is_send is null or NEW.is_send<>0 \n" +
                        " THEN \n" +
                        "  INSERT INTO `SYN_DATA` (`SYNC_DATE`,\n" +
                        "  `SYNC_TABLE_NAME`,`SYNC_TYPE`,`SYNC_PRIKEY_VALUE`,\n" +
                        "  `SYNC_PRIKEY_OLDVALUE`,`SYNC_ACTION_TAG`,`SYNC_STATUS_TAG`) \n" +
                        "  values (now(),'" + tableName + "','INCREMENT',NEW.`id`,OLD.`id`,'UPDATE',0); \n" +
                        "  END IF;\n" +
                        "  END;\n\n";
                // delete触发器
                sql += "DROP TRIGGER IF EXISTS " + "sync_tr_" + tableName + "_d; \n";
                sql += "CREATE trigger " + "sync_tr_" + tableName + "_d \n" +
                        " AFTER DELETE ON " + tableName + " \n" +
                        " FOR EACH ROW  \n" +
                        " BEGIN \n" +
                        "  INSERT INTO `SYN_DATA` (`SYNC_DATE`,\n" +
                        "  `SYNC_TABLE_NAME`,`SYNC_TYPE`,`SYNC_PRIKEY_VALUE`,\n" +
                        "  `SYNC_PRIKEY_OLDVALUE`,`SYNC_ACTION_TAG`,`SYNC_STATUS_TAG`) \n" +
                        "  values (now(),'" + tableName + "','INCREMENT',OLD.`id`,OLD.`id`,'DELETE',0); \n" +
                        "  END;\n\n";
            }
        }
        return sql;
    }


    /**
     * 生成mybatis批量插入语句 动态sql
     *
     * @param mapUnderscoreToCamelCase 是否下划线转驼峰
     */
    private static String createBatchInsertSql(boolean mapUnderscoreToCamelCase) throws Exception {
        String sql = "";
        // JdbcMysql jdbcMysql = new JdbcMysql();
        JdbcMysql jdbcMysql = new JdbcMysql(url, userName, password);
        List<Tables> tables = jdbcMysql.getAllTables(dbName);
        for (int i = 0; i < tables.size(); i++) {
            // 表名
            String tableName = tables.get(i).getName();
            System.out.println(tableName);
            if (tableName.startsWith("sys_")) {
                sql += "-- " + tableName + " \n";

                // <insert id="batchInsert">
                //  insert into user(id, username, password)
                //  values
                //  <foreach collection="list" item="item" separator=",">
                //      (#{item.id}, #{item.username}, #{item.password})
                //  </foreach>
                // </insert>

                sql += "<insert id=\"batchInsert\"> \n";
                sql += "  insert into " + tableName + "(";

                // 获取指定表字段信息
                List<TableFileds> filedList = jdbcMysql.getTable(dbName, tableName);
                for (int j = 0; j < filedList.size(); j++) {
                    TableFileds filed = filedList.get(j);
                    if (j > 0) {
                        sql += " ";
                    }
                    sql += filed.getField();
                    if (j < filedList.size() - 1) {
                        sql += ",";
                    }
                }

                sql += ") \n";
                sql += "  values \n";

                sql += "  <foreach collection=\"list\" item=\"item\" separator=\",\"> \n";

                //  (#{item.id},
                //    #{item.username},c
                //    #{item.password})
                for (int j = 0; j < filedList.size(); j++) {
                    TableFileds filed = filedList.get(j);
                    if (j > 0) {
                        sql += " ";
                    } else {
                        sql += "    (";
                    }

                    String fieldName = filed.getField();
                    if (mapUnderscoreToCamelCase) {
                        fieldName = StrUtil.toCamelCase(fieldName);
                    }
                    sql += "#{item." + fieldName + "}";

                    if (j < filedList.size() - 1) {
                        sql += ",";
                    } else {
                        sql += ")";
                    }
                }
                sql += " \n";
                sql += "  </foreach> \n";

                sql += "</insert>";
                sql += "\n\n";
            }
        }
        return sql;
    }

}